package com.cicoding.controller;

import com.cicoding.domian.KufangData;
import com.cicoding.utlis.JdbcUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.sql.*;
import java.util.*;

@RestController
@RequestMapping("/invent")
public class UpdateController {


    @PostMapping("/upload")
    public List<List<String>> uploadFile(MultipartFile file) throws Exception
    {

        List<List<String>> data = importExcel(file);
        instal(data);
        return data;
    }

    public  void instal(List<List<String>> data) throws Exception {
       /* Connection conn = JdbcUtils.getConnect();
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("select * from kufangxinxi");
        List<KufangData> list = new ArrayList<>();*/

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            // 获取数据库连接
            conn = JdbcUtils.getConnect();
            for (List<String> rowData : data) {
                // 读取 Excel 中的数据
                String column1 = rowData.get(0); // 第一列数据
                String column2 = rowData.get(1); // 第二列数据

                // 执行查询，判断是否已存在相同数据
                if (!isDataExists(conn, column1)) {
                    // 如果不存在相同数据，则执行插入操作
                    String sql = "INSERT INTO kufangxinxis (code, name) VALUES (?, ?)";
                    ps = conn.prepareStatement(sql);
                    ps.setString(1, column1);
                    ps.setString(2, column2);
                    ps.executeUpdate();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭数据库连接
            JdbcUtils.close(conn, ps);
        }

       // JdbcUtils.close(conn, rs);
        // return list;
    }

    private static boolean isDataExists(Connection conn, String column1) throws SQLException {
        String sql = "SELECT COUNT(*) AS count FROM kufangxinxi WHERE code = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, column1);
        ResultSet rs = ps.executeQuery();
        rs.next();
        int count = rs.getInt("count");
        return count > 0;
    }


  /*  public List<List<String>> importExcel(MultipartFile file) throws IOException {
        List<List<String>> data = new ArrayList<>();
        Workbook workbook = new XSSFWorkbook(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);

        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            List<String> rowData = new ArrayList<>();
            Iterator<Cell> cellIterator = row.iterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                rowData.add(getCellValueAsString(cell));
            }
            data.add(rowData);
        }

        workbook.close();
        return data;
    }


    private String getCellValueAsString(Cell cell) {
        switch (cell.getCellTypeEnum()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }
*/
    public List<List<String>> importExcel(MultipartFile file) throws IOException {
        List<List<String>> data = new ArrayList<>();
        Workbook workbook = new XSSFWorkbook(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);

        // 假设第一行是表头，我们不读取它
        int startRow = 1; // 从第二行开始读取数据

        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            // 检查当前行是否是表头行
            if (row.getRowNum() < startRow) {
                continue; // 如果是表头行，则跳过
            }

            List<String> rowData = new ArrayList<>();

            // 只读取第一列和第三列的数据
            Cell firstCell = row.getCell(1);
            Cell thirdCell = row.getCell(3); // 注意列索引是从0开始的

            // 加入判断，如果第一列和第三列都是空值，则不解析该行数据
            if (firstCell == null && thirdCell == null) {
                continue;
            }

            if (firstCell != null) {
                rowData.add(getCellValueAsString(firstCell));
            }
            if (thirdCell != null) {
                rowData.add(getCellValueAsString(thirdCell));
            }

            // 检查rowData中是否有非空数据
            if (!rowData.isEmpty() && rowData.stream().anyMatch(s -> !s.isEmpty())) {
                data.add(rowData);
            }
        }

        workbook.close();
        return data;
    }

    private String getCellValueAsString(Cell cell) {
        switch (cell.getCellTypeEnum()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }



}
